﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Text.RegularExpressions;

namespace MysqlData
{
    /// <summary>
    /// SQL语句处理
    /// </summary>
    public static class Sql
    {
        /// <summary>
        /// 获取数据条数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static string GetCountString(String sql)
        {
            sql = sql.Trim();
            sql = Regex.Replace(sql, "^select .*? from (.*) order by (.*)", "SELECT count(1) FROM $1", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, "^select .*? from (.*)", "SELECT count(1) FROM $1", RegexOptions.IgnoreCase);
            if (Regex.IsMatch(sql, ".* group by .*", RegexOptions.IgnoreCase))
            {
                //如果搜索语句中包含group by则需要在select中外包一层select才能正确统计出数据
                sql = "SELECT COUNT(1) FROM (" + sql + ") as tmp";
            }
            return sql;
        }

        /// <summary>
        /// 创建数据库连接字符串
        /// </summary>
        /// <param name="ip">数据库IP</param>
        /// <param name="username">账号</param>
        /// <param name="password">密码</param>
        /// <param name="database">数据库名称</param>
        /// <param name="charset">数据字符编码</param>
        /// <param name="port">数据库端口号</param>
        public static string CreateConnectString(string ip, string username, string password, string database, string charset = "utf8", uint port = 3306)
        {
            return string.Format("Server={0};port={4};Uid={1};Pwd={2};Database={3};CharacterSet={5}", ip, username, password, database, port, charset);
        }

        /// <summary>
        /// 生成单条SQL
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns></returns>
        public static string One(String sqlString)
        {
            Regex re = new Regex("^(.* LIMIT)(.*)", RegexOptions.IgnoreCase);
            return re.IsMatch(sqlString) ? re.Replace(sqlString, "$1 1") : sqlString+" LIMIT 1";
        }

        /// <summary>
        /// 数据绑定
        /// </summary>
        /// <param name="read">查询结果</param>
        /// <returns></returns>
        public static List<Dictionary<String, object>> ReusltList(DbDataReader read)
        {
            try
            {
                List<Dictionary<String, object>> result = null;
                if (read.HasRows)
                {
                    result = new List<Dictionary<string, object>>();
                    int num = read.FieldCount;
                    while (read.Read())
                    {
                        Dictionary<String, object> item = new Dictionary<string, object>();
                        for (int i = 0; i < num; i++){
                            item.Add(read.GetName(i), read[i] is DBNull ? null: read[i]);
                        }
                        result.Add(item);
                    }
                }
                return result;
            }
            finally
            {
                read.Close();
            }
        }
     
        /// <summary>
        /// 查询结果转换成字典
        /// </summary>
        /// <param name="read"></param>
        /// <returns></returns>
        public static Dictionary<string,object> Result(DbDataReader read)
        {
            try
            {
                Dictionary<String, object> result = null;
                if (read.HasRows && read.Read())
                {
                    result= new Dictionary<string, object>();
                    int num = read.FieldCount;
                    for (int i = 0; i < num; i++){
                        result.Add(read.GetName(i), read[i] is DBNull ? null : read[i]);
                    }
                }
                return result;
            }
            finally
            {
                read.Close();
            }
        }

        /// <summary>
        /// 转换sql语句中的参数
        /// </summary>
        /// <param name="sqlString">sql 语句</param>
        /// <param name="arg">参数</param>
        /// <returns></returns>
        public static void RegxParams(String sqlString, MySqlCommand cmd, params object[] arg)
        {
            cmd.Parameters.Clear();
            Regex reg = new Regex("@([^\\s|,|\\)]*)");
            if (arg != null && reg.IsMatch(sqlString))
            {
                MatchCollection m = reg.Matches(sqlString);
                int num = m.Count;
                int j = 0;
                for (int i = 0; i < num; i++){
                    if (cmd.Parameters.Contains(m[i].Value)){
                        continue;
                    }else if(j >= arg.Length){
                        break;
                    }else{
                        cmd.Parameters.AddWithValue(m[i].Value, arg[j]);
                        j++;
                    }
                }
            }
#if DEBUG
            Console.WriteLine("执行SQL : " + sqlString);
#endif
        }

    }
}
